<?php
class keu_rpt_model extends MY_Model {
	
	function SWReportType()
	{
		$ls_sql	= "SELECT KD_REPORT VALUE, NAMA_REPORT display
				   FROM KEU_SETUP_REPORT
				ORDER BY NAMA_REPORT";
					
		$result	= $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			return $result->result_array();
		}else{
			return array();
		}
	}
	
	function GetJSON()
	{
		$ls_sql	= "	SELECT	* 
					FROM	KEU_SETUP_REPORT_ITEM_COA 
					WHERE	(code_report = '".post('code_report')."')
					ORDER BY ".(post('sort')=='' ? 'id' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));
		return json_encode( $this->paging($ls_sql) );
	}
	
	function GetComboGridData()
	{
		$ls_sql	= "	SELECT 	'' id,
							null parent_id,
							null code_report,
							null order_num,
							'NULL' item_name,
							null numbering,
							null create_user,
							null create_date,
							null modify_user,
							null modify_date
					UNION ALL
					SELECT id,
							parent_id,
							code_report,
							order_num,
							item_name,
							numbering,
							create_user,
							create_date,
							modify_user,
							modify_date
					FROM (SELECT	* 
							FROM	KEU_SETUP_REPORT_ITEM_COA
							WHERE	(id LIKE '%".post('q')."%'
							OR item_name LIKE '%".post('q')."%') ORDER BY id) z  LIMIT 0, 10";
					
		return $this->db->query($ls_sql)->result_array();
	}
	
	function GetDetailJSON($id='')
	{
		$ls_sql	= "	SELECT	KEU_COA.*, KEU_COA.code as coa_code, a.detail_id
					FROM	KEU_SETUP_REPORT_ITEM_COA_coa a inner join KEU_COA on KEU_COA.code = a.coa_code
					where a.id = '".$id."'";
		$result	= $this->db->query($ls_sql);			
		return json_encode( $this->paging($ls_sql) );
	}
	
	
	function AddItem($arr_data){
		$this->db->insert('KEU_SETUP_REPORT_ITEM_COA', $arr_data);
	}
	
	function UpdateItem($arr_data, $arr_item){
		$this->db->update('KEU_SETUP_REPORT_ITEM_COA', $arr_data, $arr_item);
	}
	
	function GetItemSingle($as_id){
		$result = $this->db->get_where('KEU_SETUP_REPORT_ITEM_COA', array('id' => $as_id));
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}
	
	
	function GetReportType($as_report){
		$result = $this->db->get_where('keu_setup_report', array('kd_report' => $as_report));
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}
	
	function DeleteItemSingle($as_id)
	{
		try {
			$this->db->delete('KEU_SETUP_REPORT_ITEM_COA', array('id' => $as_id));
			$this->db->delete('KEU_SETUP_REPORT_ITEM_COA_coa', array('id' => $as_id));
			return '';
		} catch(Exception $e) {
			return $e->getMessage();
		}
	}
	
	function AddCoa($arr_data){
		$this->db->insert('KEU_SETUP_REPORT_ITEM_COA_coa', $arr_data);
	}
	
	function UpdateCoa($arr_data, $arr_item){
		$this->db->update('KEU_SETUP_REPORT_ITEM_COA_coa', $arr_data, $arr_item);
	}
	
	function DeleteCoaSingle($as_id)
	{
		try {
			$this->db->delete('KEU_SETUP_REPORT_ITEM_COA_coa', array('detail_id' => $as_id));
			return '';
		} catch(Exception $e) {
			return $e->getMessage();
		}
	}
	
	
	function GetReportDataNeraca($as_report_code, $adt_from='', $as_kd_kantor='%', $as_kd_cost_center='%'){
		$adt_from = ($adt_from=='' ? date('d/m/Y') : $adt_from);
		$adt_from = date_format3($adt_from, 'YYYY-MM-DD');
		
		$ls_sql	= "	  SELECT KEU_SETUP_REPORT_ITEM.*,
					 (SELECT SUM (
								CASE NVL (KEU_COA.STATUS_POS, 'D')
								   WHEN 'D' THEN NVL (gl.db, 0) - NVL (gl.cr, 0)
								   ELSE NVL (gl.cr, 0) - NVL (gl.db, 0)
								END)
								saldo
						FROM (  SELECT KEU_LEDGER.ID_COA,
									   SUM (
										  CASE 12
											 WHEN 1
											 THEN
												NVL (begin_db, 0) + NVL (db1, 0)
											 WHEN 2
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
											 WHEN 3
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
											 WHEN 4
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
											 WHEN 5
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
											 WHEN 6
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
											 WHEN 7
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
											 WHEN 8
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
											 WHEN 9
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
												+ NVL (db9, 0)
											 WHEN 10
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
												+ NVL (db9, 0)
												+ NVL (db10, 0)
											 WHEN 11
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
												+ NVL (db9, 0)
												+ NVL (db10, 0)
												+ NVL (db11, 0)
											 WHEN 12
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
												+ NVL (db9, 0)
												+ NVL (db10, 0)
												+ NVL (db11, 0)
												+ NVL (db12, 0)
										  END)
										  db,
									   SUM (
										  CASE 12
											 WHEN 1
											 THEN
												NVL (begin_cr, 0) + NVL (cr1, 0)
											 WHEN 2
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
											 WHEN 3
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
											 WHEN 4
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
											 WHEN 5
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
											 WHEN 6
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
											 WHEN 7
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
											 WHEN 8
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
											 WHEN 9
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
												+ NVL (cr9, 0)
											 WHEN 10
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
												+ NVL (cr9, 0)
												+ NVL (cr10, 0)
											 WHEN 11
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
												+ NVL (cr9, 0)
												+ NVL (cr10, 0)
												+ NVL (cr11, 0)
											 WHEN 12
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
												+ NVL (cr9, 0)
												+ NVL (cr10, 0)
												+ NVL (cr11, 0)
												+ NVL (cr12, 0)
										  END)
										  cr
								  FROM KEU_LEDGER
								 WHERE TAHUN =
										  TO_CHAR (TO_DATE ('".$adt_from."', 'YYYY-MM-DD'),
												   'YYYY')
										  - 1
									   AND kd_kantor LIKE '%".$as_kd_kantor."%'
									   AND kd_cost_center LIKE '%".$as_kd_cost_center."%'
							  GROUP BY ID_COA) gl,
							 KEU_COA,
							 KEU_SETUP_REPORT_ITEM_COA icoa
					   WHERE     gl.ID_COA = KEU_COA.ID_COA
							 AND KEU_COA.ID_COA = icoa.ID_COA
							 AND icoa.ID_ITEM = KEU_SETUP_REPORT_ITEM.ID_ITEM)
						rka,
					 (SELECT SUM (
								CASE NVL (KEU_COA.STATUS_POS, 'D')
								   WHEN 'D' THEN NVL (gl.db, 0) - NVL (gl.cr, 0)
								   ELSE NVL (gl.cr, 0) - NVL (gl.db, 0)
								END)
								saldo
						FROM (  SELECT KEU_LEDGER.ID_COA,
									   SUM (
										  CASE TO_CHAR (TO_DATE ('".$adt_from."', 'YYYY-MM-DD'),
														'mm')
											   + 0
											   + 0
											 WHEN 1
											 THEN
												NVL (begin_db, 0) + NVL (db1, 0)
											 WHEN 2
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
											 WHEN 3
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
											 WHEN 4
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
											 WHEN 5
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
											 WHEN 6
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
											 WHEN 7
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
											 WHEN 8
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
											 WHEN 9
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
												+ NVL (db9, 0)
											 WHEN 10
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
												+ NVL (db9, 0)
												+ NVL (db10, 0)
											 WHEN 11
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
												+ NVL (db9, 0)
												+ NVL (db10, 0)
												+ NVL (db11, 0)
											 WHEN 12
											 THEN
												  NVL (begin_db, 0)
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
												+ NVL (db9, 0)
												+ NVL (db10, 0)
												+ NVL (db11, 0)
												+ NVL (db12, 0)
										  END)
										  db,
									   SUM (
										  CASE TO_CHAR (TO_DATE ('".$adt_from."', 'YYYY-MM-DD'),
														'mm')
											   + 0
											 WHEN 1
											 THEN
												NVL (begin_cr, 0) + NVL (cr1, 0)
											 WHEN 2
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
											 WHEN 3
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
											 WHEN 4
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
											 WHEN 5
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
											 WHEN 6
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
											 WHEN 7
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
											 WHEN 8
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
											 WHEN 9
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
												+ NVL (cr9, 0)
											 WHEN 10
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
												+ NVL (cr9, 0)
												+ NVL (cr10, 0)
											 WHEN 11
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
												+ NVL (cr9, 0)
												+ NVL (cr10, 0)
												+ NVL (cr11, 0)
											 WHEN 12
											 THEN
												  NVL (begin_cr, 0)
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
												+ NVL (cr9, 0)
												+ NVL (cr10, 0)
												+ NVL (cr11, 0)
												+ NVL (cr12, 0)
										  END)
										  cr
								  FROM KEU_LEDGER
								 WHERE TAHUN =
										  TO_CHAR (TO_DATE ('".$adt_from."', 'YYYY-MM-DD'),
												   'YYYY')
									   AND kd_kantor LIKE '%".$as_kd_kantor."%'
									   AND kd_cost_center LIKE '%".$as_kd_cost_center."%'
							  GROUP BY ID_COA) gl,
							 KEU_COA,
							 KEU_SETUP_REPORT_ITEM_COA icoa
					   WHERE     gl.ID_COA = KEU_COA.ID_COA
							 AND KEU_COA.ID_COA = icoa.ID_COA
							 AND icoa.ID_ITEM = KEU_SETUP_REPORT_ITEM.ID_ITEM)
						realisasi,
					 (SELECT COUNT (1)
						FROM KEU_SETUP_REPORT_ITEM tmp
					   WHERE tmp.ID_ITEM_INDUK = KEU_SETUP_REPORT_ITEM.ID_ITEM)
						numchild,
					 (SELECT COUNT (1)
						FROM KEU_SETUP_REPORT_ITEM_COA tmp
					   WHERE tmp.ID_ITEM = KEU_SETUP_REPORT_ITEM.ID_ITEM)
						item_coa,
					 0 LEVEL_ID
				FROM KEU_SETUP_REPORT_ITEM
			   WHERE KD_REPORT = '".$as_report_code."' AND NVL (ID_ITEM_INDUK, ' ') = ' '
			ORDER BY NO_URUT, ID_ITEM";
					
		
		$result	= $this->db->query($ls_sql);
		
		if($result->num_rows() > 0){
			$arr_data	= $result->result_array();
			
			$arr_return = array();
			
			for($i=0,$max_i=count($arr_data); $i<$max_i; $i++){
				array_push($arr_return, $arr_data[$i]);
				if(intval($arr_data[$i]['numchild']) > 0){
					$arr_return = array_merge($arr_return, $this->GetReportSubDataNeraca($as_report_code, $arr_data[$i]['id_item'], $arr_data[$i]['level_id'], $adt_from, $as_kd_kantor,$as_kd_cost_center));
				}
			}
			
			return $arr_return;
		}else{
			return NULL;
		}
	}
	
	function GetReportSubDataNeraca($as_report_code, $parent_id='', $ai_level=0, $adt_from='', $as_kd_kantor='%', $as_kd_cost_center='%'){
		if($parent_id=='') return NULL;
		
		$adt_from = ($adt_from=='' ? date('d/m/Y') : $adt_from);
		$adt_from = date_format3($adt_from, 'YYYY-MM-DD');
		
		$ls_sql	= "	SELECT
					KEU_SETUP_REPORT_ITEM.*,(
						SELECT
							sum(
								CASE NVL(KEU_COA.STATUS_POS, 'D')
								WHEN 'D' THEN
									NVL(gl.db, 0)- NVL(gl.cr, 0)
								ELSE
									NVL(gl.cr, 0)- NVL(gl.db, 0)
								END
							)saldo
						FROM
							(
								SELECT
									KEU_LEDGER.ID_COA,
									sum(
										CASE 12
										WHEN 1 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)
										WHEN 2 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)
										WHEN 3 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)
										WHEN 4 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)
										WHEN 5 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)
										WHEN 6 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)
										WHEN 7 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)
										WHEN 8 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)
										WHEN 9 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)
										WHEN 10 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)+ NVL(db10, 0)
										WHEN 11 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)+ NVL(db10, 0)+ NVL(db11, 0)
										WHEN 12 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)+ NVL(db10, 0)+ NVL(db11, 0)+ NVL(db12, 0)
										END
									)db,
									sum(
										CASE 12
										WHEN 1 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)
										WHEN 2 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)
										WHEN 3 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)
										WHEN 4 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)
										WHEN 5 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)
										WHEN 6 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)
										WHEN 7 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)
										WHEN 8 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)
										WHEN 9 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)
										WHEN 10 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)+ NVL(cr10, 0)
										WHEN 11 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)+ NVL(cr10, 0)+ NVL(cr11, 0)
										WHEN 12 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)+ NVL(cr10, 0)+ NVL(cr11, 0)+ NVL(cr12, 0)
										END
									)cr
								FROM
									KEU_LEDGER
								WHERE
									TAHUN =
										  TO_CHAR (TO_DATE ('".$adt_from."', 'YYYY-MM-DD'),
												   'YYYY')
										  - 1
									   AND kd_kantor LIKE '%".$as_kd_kantor."%'
									   AND kd_cost_center LIKE '%".$as_kd_cost_center."%'
								GROUP BY
									ID_COA
							)gl,
							KEU_COA,
							KEU_SETUP_REPORT_ITEM_COA icoa
						WHERE     gl.ID_COA = KEU_COA.ID_COA
							 AND KEU_COA.ID_COA = icoa.ID_COA
							 AND icoa.ID_ITEM = KEU_SETUP_REPORT_ITEM.ID_ITEM)
					rka,
					(
						SELECT
							sum(
								CASE NVL(KEU_COA.STATUS_POS, 'D')
								WHEN 'D' THEN
									NVL(gl.db, 0)- NVL(gl.cr, 0)
								ELSE
									NVL(gl.cr, 0)- NVL(gl.db, 0)
								END
							)saldo
						FROM
							(
								SELECT
									KEU_LEDGER.ID_COA,
									sum(
										CASE to_char(to_date('".$adt_from."', 'YYYY-MM-DD'),'mm')+0
										WHEN 1 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)
										WHEN 2 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)
										WHEN 3 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)
										WHEN 4 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)
										WHEN 5 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)
										WHEN 6 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)
										WHEN 7 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)
										WHEN 8 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)
										WHEN 9 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)
										WHEN 10 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)+ NVL(db10, 0)
										WHEN 11 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)+ NVL(db10, 0)+ NVL(db11, 0)
										WHEN 12 THEN
											NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)+ NVL(db10, 0)+ NVL(db11, 0)+ NVL(db12, 0)
										END
									)db,
									sum(
										CASE to_char(to_date('".$adt_from."', 'YYYY-MM-DD'),'mm')+0
										WHEN 1 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)
										WHEN 2 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)
										WHEN 3 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)
										WHEN 4 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)
										WHEN 5 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)
										WHEN 6 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)
										WHEN 7 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)
										WHEN 8 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)
										WHEN 9 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)
										WHEN 10 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)+ NVL(cr10, 0)
										WHEN 11 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)+ NVL(cr10, 0)+ NVL(cr11, 0)
										WHEN 12 THEN
											NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)+ NVL(cr10, 0)+ NVL(cr11, 0)+ NVL(cr12, 0)
										END
									)cr
								FROM
									KEU_LEDGER
								WHERE
									TAHUN =
										  TO_CHAR (TO_DATE ('".$adt_from."', 'YYYY-MM-DD'),
												   'YYYY')
									   AND kd_kantor LIKE '%".$as_kd_kantor."%'
									   AND kd_cost_center LIKE '%".$as_kd_cost_center."%'
								GROUP BY
									ID_COA
							)gl,
							KEU_COA,
							KEU_SETUP_REPORT_ITEM_COA icoa
						WHERE     gl.ID_COA = KEU_COA.ID_COA
							 AND KEU_COA.ID_COA = icoa.ID_COA
							 AND icoa.ID_ITEM = KEU_SETUP_REPORT_ITEM.ID_ITEM)
						realisasi,
					(SELECT COUNT(1) FROM KEU_SETUP_REPORT_ITEM tmp WHERE tmp.id_item_induk = KEU_SETUP_REPORT_ITEM.id_item) numchild,
					(SELECT COUNT(1) FROM KEU_SETUP_REPORT_ITEM_COA tmp WHERE tmp.id_item = KEU_SETUP_REPORT_ITEM.id_item) item_coa,
					".($ai_level+1)." level_id
				FROM
					KEU_SETUP_REPORT_ITEM
				WHERE
					kd_report = '".$as_report_code."' AND
					id_item_induk = '".$parent_id."'
				ORDER BY
					no_urut, id_item";
					
		
		$result	= $this->db->query($ls_sql);
		
		if($result->num_rows() > 0){
			$arr_data	= $result->result_array();
			
			$arr_return = array();
			
			for($i=0,$max_i=count($arr_data); $i<$max_i; $i++){
				array_push($arr_return, $arr_data[$i]);
				if(intval($arr_data[$i]['numchild']) > 0){
					$arr_return = array_merge($arr_return, $this->GetReportSubDataNeraca($as_report_code, $arr_data[$i]['id_item'], $arr_data[$i]['level_id'], $adt_from,$as_kd_kantor,$as_kd_cost_center));
				}
			}
			
			return $arr_return;
		}else{
			return NULL;
		}
	}
	
	
	
	
	
	function GetReportDataArusKas($as_report_code, $adt_from='', $as_kd_kantor='%', $as_kd_cost_center='%'){
		$adt_from = ($adt_from=='' ? date('d/m/Y') : $adt_from);
		$adt_from = date_format3($adt_from, 'YYYY-MM-DD');
		
		$ls_sql	= "	  SELECT KEU_SETUP_REPORT_ITEM.*,
					 (SELECT SUM (
								CASE NVL (KEU_COA.STATUS_POS, 'D')
								   WHEN 'D' THEN NVL (gl.db, 0) - NVL (gl.cr, 0)
								   ELSE NVL (gl.cr, 0) - NVL (gl.db, 0)
								END)
								saldo
						FROM (  SELECT KEU_LEDGER.ID_COA,
									   SUM (
										  CASE 12
											 WHEN 1
											 THEN
												0 + NVL (db1, 0)
											 WHEN 2
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
											 WHEN 3
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
											 WHEN 4
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
											 WHEN 5
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
											 WHEN 6
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
											 WHEN 7
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
											 WHEN 8
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
											 WHEN 9
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
												+ NVL (db9, 0)
											 WHEN 10
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
												+ NVL (db9, 0)
												+ NVL (db10, 0)
											 WHEN 11
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
												+ NVL (db9, 0)
												+ NVL (db10, 0)
												+ NVL (db11, 0)
											 WHEN 12
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
												+ NVL (db9, 0)
												+ NVL (db10, 0)
												+ NVL (db11, 0)
												+ NVL (db12, 0)
										  END)
										  db,
									   SUM (
										  CASE 12
											 WHEN 1
											 THEN
												0 + NVL (cr1, 0)
											 WHEN 2
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
											 WHEN 3
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
											 WHEN 4
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
											 WHEN 5
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
											 WHEN 6
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
											 WHEN 7
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
											 WHEN 8
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
											 WHEN 9
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
												+ NVL (cr9, 0)
											 WHEN 10
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
												+ NVL (cr9, 0)
												+ NVL (cr10, 0)
											 WHEN 11
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
												+ NVL (cr9, 0)
												+ NVL (cr10, 0)
												+ NVL (cr11, 0)
											 WHEN 12
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
												+ NVL (cr9, 0)
												+ NVL (cr10, 0)
												+ NVL (cr11, 0)
												+ NVL (cr12, 0)
										  END)
										  cr
								  FROM KEU_LEDGER
								 WHERE TAHUN =
										  TO_CHAR (TO_DATE ('".$adt_from."', 'YYYY-MM-DD'),
												   'YYYY')
										  - 1
									   AND kd_kantor LIKE '%".$as_kd_kantor."%'
									   AND kd_cost_center LIKE '%".$as_kd_cost_center."%'
							  GROUP BY ID_COA) gl,
							 KEU_COA,
							 KEU_SETUP_REPORT_ITEM_COA icoa
					   WHERE     gl.ID_COA = KEU_COA.ID_COA
							 AND KEU_COA.ID_COA = icoa.ID_COA
							 AND icoa.ID_ITEM = KEU_SETUP_REPORT_ITEM.ID_ITEM)
						rka,
					 (SELECT SUM (
								CASE NVL (KEU_COA.STATUS_POS, 'D')
								   WHEN 'D' THEN NVL (gl.db, 0) - NVL (gl.cr, 0)
								   ELSE NVL (gl.cr, 0) - NVL (gl.db, 0)
								END)
								saldo
						FROM (  SELECT KEU_LEDGER.ID_COA,
									   SUM (
										  CASE TO_CHAR (TO_DATE ('".$adt_from."', 'YYYY-MM-DD'),
														'mm')
											   + 0
											   + 0
											 WHEN 1
											 THEN
												0 + NVL (db1, 0)
											 WHEN 2
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
											 WHEN 3
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
											 WHEN 4
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
											 WHEN 5
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
											 WHEN 6
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
											 WHEN 7
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
											 WHEN 8
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
											 WHEN 9
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
												+ NVL (db9, 0)
											 WHEN 10
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
												+ NVL (db9, 0)
												+ NVL (db10, 0)
											 WHEN 11
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
												+ NVL (db9, 0)
												+ NVL (db10, 0)
												+ NVL (db11, 0)
											 WHEN 12
											 THEN
												  0
												+ NVL (db1, 0)
												+ NVL (db2, 0)
												+ NVL (db3, 0)
												+ NVL (db4, 0)
												+ NVL (db5, 0)
												+ NVL (db6, 0)
												+ NVL (db7, 0)
												+ NVL (db8, 0)
												+ NVL (db9, 0)
												+ NVL (db10, 0)
												+ NVL (db11, 0)
												+ NVL (db12, 0)
										  END)
										  db,
									   SUM (
										  CASE TO_CHAR (TO_DATE ('".$adt_from."', 'YYYY-MM-DD'),
														'mm')
											   + 0
											 WHEN 1
											 THEN
												0 + NVL (cr1, 0)
											 WHEN 2
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
											 WHEN 3
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
											 WHEN 4
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
											 WHEN 5
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
											 WHEN 6
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
											 WHEN 7
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
											 WHEN 8
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
											 WHEN 9
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
												+ NVL (cr9, 0)
											 WHEN 10
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
												+ NVL (cr9, 0)
												+ NVL (cr10, 0)
											 WHEN 11
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
												+ NVL (cr9, 0)
												+ NVL (cr10, 0)
												+ NVL (cr11, 0)
											 WHEN 12
											 THEN
												  0
												+ NVL (cr1, 0)
												+ NVL (cr2, 0)
												+ NVL (cr3, 0)
												+ NVL (cr4, 0)
												+ NVL (cr5, 0)
												+ NVL (cr6, 0)
												+ NVL (cr7, 0)
												+ NVL (cr8, 0)
												+ NVL (cr9, 0)
												+ NVL (cr10, 0)
												+ NVL (cr11, 0)
												+ NVL (cr12, 0)
										  END)
										  cr
								  FROM KEU_LEDGER
								 WHERE TAHUN =
										  TO_CHAR (TO_DATE ('".$adt_from."', 'YYYY-MM-DD'),
												   'YYYY')
									   AND kd_kantor LIKE '%".$as_kd_kantor."%'
									   AND kd_cost_center LIKE '%".$as_kd_cost_center."%'
							  GROUP BY ID_COA) gl,
							 KEU_COA,
							 KEU_SETUP_REPORT_ITEM_COA icoa
					   WHERE     gl.ID_COA = KEU_COA.ID_COA
							 AND KEU_COA.ID_COA = icoa.ID_COA
							 AND icoa.ID_ITEM = KEU_SETUP_REPORT_ITEM.ID_ITEM)
						realisasi,
					 (SELECT COUNT (1)
						FROM KEU_SETUP_REPORT_ITEM tmp
					   WHERE tmp.ID_ITEM_INDUK = KEU_SETUP_REPORT_ITEM.ID_ITEM)
						numchild,
					 (SELECT COUNT (1)
						FROM KEU_SETUP_REPORT_ITEM_COA tmp
					   WHERE tmp.ID_ITEM = KEU_SETUP_REPORT_ITEM.ID_ITEM)
						item_coa,
					 0 LEVEL_ID
				FROM KEU_SETUP_REPORT_ITEM
			   WHERE KD_REPORT = '".$as_report_code."' AND NVL (ID_ITEM_INDUK, ' ') = ' '
			ORDER BY NO_URUT, ID_ITEM";
		

		$result	= $this->db->query($ls_sql);
		
		if($result->num_rows() > 0){
			$arr_data	= $result->result_array();
			
			$arr_return = array();
			
			for($i=0,$max_i=count($arr_data); $i<$max_i; $i++){
				array_push($arr_return, $arr_data[$i]);
				if(intval($arr_data[$i]['numchild']) > 0){
					$arr_return = array_merge($arr_return, $this->GetReportSubDataArusKas($as_report_code, $arr_data[$i]['id_item'], $arr_data[$i]['level_id'], $adt_from, $as_kd_kantor,$as_kd_cost_center));
				}
			}
			
			return $arr_return;
		}else{
			return NULL;
		}
	}
	
	function GetReportSubDataArusKas($as_report_code, $parent_id='', $ai_level=0, $adt_from='', $as_kd_kantor='%', $as_kd_cost_center='%'){
		if($parent_id=='') return NULL;
		
		$adt_from = ($adt_from=='' ? date('d/m/Y') : $adt_from);
		$adt_from = date_format3($adt_from, 'YYYY-MM-DD');
		
		$ls_sql	= "	SELECT
					KEU_SETUP_REPORT_ITEM.*,(
						SELECT
							sum(
								CASE NVL(KEU_COA.STATUS_POS, 'D')
								WHEN 'D' THEN
									NVL(gl.db, 0)- NVL(gl.cr, 0)
								ELSE
									NVL(gl.cr, 0)- NVL(gl.db, 0)
								END
							)saldo
						FROM
							(
								SELECT
									KEU_LEDGER.ID_COA,
									sum(
										CASE 12
										WHEN 1 THEN
											0+ NVL(db1, 0)
										WHEN 2 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)
										WHEN 3 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)
										WHEN 4 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)
										WHEN 5 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)
										WHEN 6 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)
										WHEN 7 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)
										WHEN 8 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)
										WHEN 9 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)
										WHEN 10 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)+ NVL(db10, 0)
										WHEN 11 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)+ NVL(db10, 0)+ NVL(db11, 0)
										WHEN 12 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)+ NVL(db10, 0)+ NVL(db11, 0)+ NVL(db12, 0)
										END
									)db,
									sum(
										CASE 12
										WHEN 1 THEN
											0+ NVL(cr1, 0)
										WHEN 2 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)
										WHEN 3 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)
										WHEN 4 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)
										WHEN 5 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)
										WHEN 6 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)
										WHEN 7 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)
										WHEN 8 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)
										WHEN 9 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)
										WHEN 10 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)+ NVL(cr10, 0)
										WHEN 11 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)+ NVL(cr10, 0)+ NVL(cr11, 0)
										WHEN 12 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)+ NVL(cr10, 0)+ NVL(cr11, 0)+ NVL(cr12, 0)
										END
									)cr
								FROM
									KEU_LEDGER
								WHERE
									TAHUN =
										  TO_CHAR (TO_DATE ('".$adt_from."', 'YYYY-MM-DD'),
												   'YYYY')
										  - 1
									   AND kd_kantor LIKE '%".$as_kd_kantor."%'
									   AND kd_cost_center LIKE '%".$as_kd_cost_center."%'
								GROUP BY
									ID_COA
							)gl,
							KEU_COA,
							KEU_SETUP_REPORT_ITEM_COA icoa
						WHERE     gl.ID_COA = KEU_COA.ID_COA
							 AND KEU_COA.ID_COA = icoa.ID_COA
							 AND icoa.ID_ITEM = KEU_SETUP_REPORT_ITEM.ID_ITEM)
					rka,
					(
						SELECT
							sum(
								CASE NVL(KEU_COA.STATUS_POS, 'D')
								WHEN 'D' THEN
									NVL(gl.db, 0)- NVL(gl.cr, 0)
								ELSE
									NVL(gl.cr, 0)- NVL(gl.db, 0)
								END
							)saldo
						FROM
							(
								SELECT
									KEU_LEDGER.ID_COA,
									sum(
										CASE to_char(to_date('".$adt_from."', 'YYYY-MM-DD'),'mm')+0
										WHEN 1 THEN
											0+ NVL(db1, 0)
										WHEN 2 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)
										WHEN 3 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)
										WHEN 4 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)
										WHEN 5 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)
										WHEN 6 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)
										WHEN 7 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)
										WHEN 8 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)
										WHEN 9 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)
										WHEN 10 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)+ NVL(db10, 0)
										WHEN 11 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)+ NVL(db10, 0)+ NVL(db11, 0)
										WHEN 12 THEN
											0+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)+ NVL(db10, 0)+ NVL(db11, 0)+ NVL(db12, 0)
										END
									)db,
									sum(
										CASE to_char(to_date('".$adt_from."', 'YYYY-MM-DD'),'mm')+0
										WHEN 1 THEN
											0+ NVL(cr1, 0)
										WHEN 2 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)
										WHEN 3 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)
										WHEN 4 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)
										WHEN 5 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)
										WHEN 6 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)
										WHEN 7 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)
										WHEN 8 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)
										WHEN 9 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)
										WHEN 10 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)+ NVL(cr10, 0)
										WHEN 11 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)+ NVL(cr10, 0)+ NVL(cr11, 0)
										WHEN 12 THEN
											0+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)+ NVL(cr10, 0)+ NVL(cr11, 0)+ NVL(cr12, 0)
										END
									)cr
								FROM
									KEU_LEDGER
								WHERE
									TAHUN =
										  TO_CHAR (TO_DATE ('".$adt_from."', 'YYYY-MM-DD'),
												   'YYYY')
									   AND kd_kantor LIKE '%".$as_kd_kantor."%'
									   AND kd_cost_center LIKE '%".$as_kd_cost_center."%'
								GROUP BY
									ID_COA
							)gl,
							KEU_COA,
							KEU_SETUP_REPORT_ITEM_COA icoa
						WHERE     gl.ID_COA = KEU_COA.ID_COA
							 AND KEU_COA.ID_COA = icoa.ID_COA
							 AND icoa.ID_ITEM = KEU_SETUP_REPORT_ITEM.ID_ITEM)
						realisasi,
					(SELECT COUNT(1) FROM KEU_SETUP_REPORT_ITEM tmp WHERE tmp.id_item_induk = KEU_SETUP_REPORT_ITEM.id_item) numchild,
					(SELECT COUNT(1) FROM KEU_SETUP_REPORT_ITEM_COA tmp WHERE tmp.id_item = KEU_SETUP_REPORT_ITEM.id_item) item_coa,
					".($ai_level+1)." level_id
				FROM
					KEU_SETUP_REPORT_ITEM
				WHERE
					kd_report = '".$as_report_code."' AND
					id_item_induk = '".$parent_id."'
				ORDER BY
					no_urut, id_item";
					
		
		
		$result	= $this->db->query($ls_sql);
		
		if($result->num_rows() > 0){
			$arr_data	= $result->result_array();
			
			$arr_return = array();
			
			for($i=0,$max_i=count($arr_data); $i<$max_i; $i++){
				array_push($arr_return, $arr_data[$i]);
				if(intval($arr_data[$i]['numchild']) > 0){
					$arr_return = array_merge($arr_return, $this->GetReportSubDataNeraca($as_report_code, $arr_data[$i]['id_item'], $arr_data[$i]['level_id'], $adt_from,$as_kd_kantor,$as_kd_cost_center));
				}
			}
			
			return $arr_return;
		}else{
			return NULL;
		}
	}
	
	
}
?>